/* ********************************************************************************
 *      Copyright:  (C) 2025 ZYT
 *                All rights reserved.
 *    
 *       Filename:  sqlite.c
 *    Description:  This file 
 *                        
 *        Version:  1.0.0(01/04/25)
 *         Author:  Zhang yuting <zhangyuting@163.com>
 *      ChangeLog:  1, Release initial version on "01/04/25 20:36:35"
 *      
 *            
 *********************************************************************************/

#include "sqlite.h"

static int                  callback_count = 0;
//return value:  On error, -1 is returned. On success, 0 is returned.
int sql_init(sqlite3 **db, const char *table_name)
{
	char 			sql_buffer[512] = {0};
	char           *zerrmsg = 0;
	int             rv;

	rv = sqlite3_open("data.db", db);

	if(rv != SQLITE_OK )
	{
		log_error("open database failure: %s.\n", zerrmsg);
		return -1;
	}
	log_info("open database successfully.\n");

	snprintf(sql_buffer, sizeof(sql_buffer),
			"CREATE TABLE IF NOT EXISTS %s ("
			"HOST    CHAR(20) NOT NULL CHECK(LENGTH(HOST) <= 20), "
			"TIME    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "
			"TEMPER  REAL NOT NULL CHECK(TEMPER BETWEEN -50 AND 100)"
			");", 
			table_name);

	rv = sqlite3_exec(*db, sql_buffer, NULL, NULL, &zerrmsg);

	if (rv != SQLITE_OK)
	{
		log_error("create_statement failure: %s.\n", zerrmsg);
		return -1;
	}
	log_info("create statement[%s] successfully.\n", table_name);


	return 0;
}


int sql_insert(sqlite3 *db, char *statement_name, packet_t *pack)
{
	char        buffer[128];
	char       *insert_err;
	
	if( NULL == pack )
	{
		log_error("NULL pointer");
		return -1;
	}

	sprintf(buffer, "INSERT INTO %s VALUES ('%s', '%s', '%.4f')", statement_name, pack->dev_sn, pack->dev_time, pack->dev_temp);

	if( sqlite3_exec(db, buffer, 0, 0, &insert_err) != SQLITE_OK )
	{
		log_error("insert data to %s failure: %s\n", statement_name, insert_err);
		return -1;
	}
	log_info("insert data[%s %s %.4f] to %s successfully\n", pack->dev_sn, pack->dev_time, pack->dev_temp,statement_name);

	return 0;
}

//return value:  Non-exist, 0 is returned. Exist, 1 is returned. On error, -1 is returned.
int sql_check(sqlite3 *db, char *statement_name)
{
	char 		 sql_cmd[64];
	char 		*check_err = NULL;
	char       **dbResult;
	int          nRow = 0;
	int          nColumn = 0;

	sprintf(sql_cmd, "SELECT * FROM %s limit 1;", statement_name);

	if( sqlite3_get_table(db, sql_cmd, &dbResult, &nRow, &nColumn, &check_err) != SQLITE_OK ) 
	{
		log_error("sqlite3_get_table failure.\n");
		sqlite3_free_table(dbResult);
		return -1;
	}

	sqlite3_free_table(dbResult);

	if(  0 == nRow )
		return -1;
	else
		return 0;
}

int sql_delete(sqlite3 *db, char *statement_name)
{
	char        sql_cmd[64];
	char       *delete_err;

	sprintf(sql_cmd, "DELETE from %s limit 1;", statement_name);

	if( sqlite3_exec(db, sql_cmd, 0, 0, &delete_err) != SQLITE_OK )
	{
		log_error("delete sended data failure.\n");
		return -1;
	}

	log_info("delete sended data sucessfully.\n");

	return 0;
}

int sql_query(sqlite3 *db, packet_t *pack)
{   
	char            sql_cmd[512];      
	int             rv = -1;
	char            *sqlError = NULL;

	if ( !pack )
	{       
		return 0;
	}       

	memset(sql_cmd, 0, sizeof(sql_cmd));
	sprintf(sql_cmd, "SELECT * FROM [%s] limit 1 offset 0;", pack->dev_sn);
	rv = sqlite3_exec(db, sql_cmd, query_callback, pack, &sqlError);
	if ( rv != SQLITE_OK )
	{       
		log_warn("Failed to select data from table: %s\n", sqlError);
		sqlite3_free(sqlError);
		return 0;
	}

	//判断是否有数据
		if (callback_count == 0)
		{
			return 0;
		}

	callback_count = 0;

	return 1;
}

static int query_callback(void *exec_para, int n_column, char **column_value, char **column_name)
{
	packet_t               *pack = (packet_t *)exec_para;
	float                   temp;

	callback_count++;

	temp = strtof(column_value[2], NULL);
	strncpy(pack->dev_time, column_value[1], sizeof(pack->dev_time) - 1);
	pack->dev_temp = temp;

	return 0;
}

//return value:  On error, -1 is returned. On success, size of data is returned.
int sql_write_to_server(sqlite3 *db, int sockfd, char *statement_name)
{
	char        *write_err;
	char         sql_cmd[128];
	int          fd = sockfd;

	sprintf(sql_cmd, "SELECT * FROM %s limit 1;", statement_name);

	if( sqlite3_exec(db, sql_cmd, callback_write, (void *)&fd, &write_err) != SQLITE_OK )
	{
		log_error("sql_write_to_server failure.\n");
		return -1;
	}
	return fd;
}
/*  SQLite 数据库查询回调函数，用于将查询结果写入文件描述符 */ 
static int callback_write(void *data, int argc, char **argv, char **azColName)
{
	char        buffer[128];
	int         fd = *(int *)data;
	int         rv;

	memset(buffer, 0, sizeof(buffer));

	sprintf(buffer, "%s | %s | %s", argv[0], argv[1], argv[2]);

	rv = write(fd, buffer, strlen(buffer));

	if( rv < 0 )
	{
		*(int *)data = -1;
	}
	*(int *)data = rv;

	return 0;
}

int sql_close(sqlite3 **db)
{
	int rv = sqlite3_close(*db);
	if(rv != SQLITE_OK )
	{
		const char *errmsg = sqlite3_errmsg(*db);
		log_error("Failed to close database: %s\n", errmsg);
		return -1;
	}
	*db = NULL; // 关闭成功后置空指针
	log_info("Database closed successfully.\n");
	return 0;
}
